import pymysql
import re


class update:
    def __init__(self):
        self.db = None
        self.cursor = None
        self.mysql_server = {  #!数据库链接配置
            "host": "159.75.73.211",
            "user": "root",
            "passwd": "GZCXSWKJFZYXGS",
            "port": 3306,
            "db": "order_list",
            "cursorclass": pymysql.cursors.DictCursor,
        }

    def conn(self):
        try:
            self.db = pymysql.connect(**self.mysql_server)
            self.cursor = self.db.cursor()  #!创建数据游标
            print("mysql连接成功！")
        except Exception as e:
            raise e

    def ins_del_upd(
        self, sql: str
    ) -> (bool, dict):  # type:ignore #!数据库添加、删除、修改
        try:
            rowcount = self.cursor.execute(sql)
            self.db.commit()
            print("{}操作成功".format(sql.split()[0]))
            return True, {"rowcount": rowcount}
        except Exception as e:
            print("操作失败：{}".format({"error": e, "sql": sql}))
            self.db.rollback()
            return False, {"error": e, "sql": sql}

    # 数据库查询
    def select(self, sql: str) -> (bool, dict):  # type:ignore #!数据搜索
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            # 获取所有记录列表
            results = self.cursor.fetchall()
            self.db.commit()
            return True, results
        except Exception as e:
            return False, {"error": e, "sql": sql}

    def main(self):
        reg = re.compile(r"(\d{2,4}\.\d{1,2}\.\d{1,2})")
        self.conn()
        for label in ["CxOrder"]:
            ok, data = self.select(
                "select `OrderNo`,`Note`,`ReceivedPayment` from {} where  `Status` !='已回款'".format(
                    label
                )
            )
            print("select `OrderNo`,`Note`,`ReceivedPayment` from {} where  `Status` !='已回款'".format(
                    label
                ))
            if not ok:
                raise Exception("数据库获取数据错误,err:{}".format(data))
            for i in data:

                setV=""
                if date := reg.findall(i["Note"]):
                    # print(date)
                    updateDate = ""
                    b = date[-1].split(".")
                    updateDate = "{}-{}-{}".format(
                        b[0] if len(b[0]) == 4 else "20{}".format(b[0]),
                        "{}{}".format("0" * (2 - len(b[1])), b[1]),
                        "{}{}".format("0" * (2 - len(b[2])), b[2]),
                    )
                    setV+="`UrgeDate` = '{}'".format( updateDate)
                if "款" in i["Note"] or "作废" in i["Note"] or i["ReceivedPayment"] == "是":
                    print(i['OrderNo'])
                    setV+="{} `ReceivedPayment` = '是'".format("," if setV else "")
                    setV+=",`Status`='已回款'"
                if not setV:
                    continue
                if i["OrderNo"] =="DC202212032427101":
                    print("update {} set {} where `OrderNo`='{}'".format(
                        label,setV, i["OrderNo"]
                    ))
                self.ins_del_upd(
                    "update {} set {} where `OrderNo`='{}'".format(
                        label,setV, i["OrderNo"]
                    )
                )


# if __name__ == "__main__":
#     update().main()
